﻿--'trade'
--KH trade mới là 1. KH Trade mới toe của VPB
--                2. KH Trade đó ngừng các hoạt động giao dịch Trade và giao dịch quay trở lại sau ít nhất 6 tháng.


-------------- TRONG CHỈ TIÊU KPI THEO DÕI THÌ KH TRADE MỚI K TÍNH ĐẾN CÁC KH CHUỖI
-----====>> ĐỂ KH MỚI CỦA CHUỖI TRONG 1 BÁO CÁO RIÊNG 

----'CHAY TIEP'
--DECLARE @DATE VARCHAR (100)
--SET @DATE = '20160530'

--DECLARE @DATE_dau_thang VARCHAR (100)
--SET @DATE_dau_thang = '20160501'

--DECLARE @DATE_cuoi_thang VARCHAR (100)
--SET @DATE_cuoi_thang = '20160531'

--DECLARE @YEAR_NEW VARCHAR (100)
--SET @YEAR_NEW = '2016'


--select max(from_date) from  SERVER12.ANHCP.DBO.KH_TRADE_MOI

declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
DECLARE @DATE VARCHAR (100)
DECLARE @DATE_dau_thang VARCHAR (100)
DECLARE @DATE_cuoi_thang VARCHAR (100)
DECLARE @YEAR_NEW VARCHAR (100)
SET @YEAR_NEW = '2016'
set @DATE = @date_update

DECLARE @NGAY_6THANG_TRUOC VARCHAR (100)

--DECLARE _cursor CURSOR FOR
--SELECT business_date FROM QUYNHNN3.TRADE2.dbo.business_date where business_date>'20160815' order by business_date ------------ thay ngày
--OPEN _cursor
--FETCH NEXT FROM _cursor INTO  @DATE;
--WHILE @@FETCH_STATUS=0
--BEGIN
 
 SET @DATE_dau_thang = LEFT(@DATE, 6) + '01'
 SET @DATE_cuoi_thang = CASE
 WHEN LEFT(@DATE, 6) = '201612' then LEFT(@DATE, 6) + '31'
  WHEN LEFT(@DATE, 6) = '201611' then LEFT(@DATE, 6) + '30'
 WHEN LEFT(@DATE, 6) = '201610' then LEFT(@DATE, 6) + '31'
 WHEN LEFT(@DATE, 6) = '201609' then LEFT(@DATE, 6) + '30'
 WHEN LEFT(@DATE, 6) = '201608' then LEFT(@DATE, 6) + '31'
 WHEN LEFT(@DATE, 6) = '201607' then LEFT(@DATE, 6) + '30'
						   WHEN LEFT(@DATE, 6) = '201605' then LEFT(@DATE, 6) + '31'
	                       WHEN LEFT(@DATE, 6) = '201606' then LEFT(@DATE, 6) + '30'
						   when LEFT(@DATE, 6) = '201604' then LEFT(@DATE, 6) + '29'
						   when LEFT(@DATE, 6) = '201603' then LEFT(@DATE, 6) + '31'
						   when LEFT(@DATE, 6) = '201602' then LEFT(@DATE, 6) + '29'
						   when LEFT(@DATE, 6) = '201601' then LEFT(@DATE, 6) + '30'
						  						  END

SET @NGAY_6THANG_TRUOC = left(convert(varchar, dateadd(MONTH, -6, @DATE), 112), 10)

print (''+@DATE+'')
print (''+@DATE_dau_thang+'')
print (''+@DATE_cuoi_thang+'')
print (''+@NGAY_6THANG_TRUOC+'')
--------select * from SERVER12.ANHCP.DBO.KH_TRADE_MOI  where from_Date='20160408'

print (''+@DATE+'')
-----4. Tài trợ xuất - nhập khẩu, Chiết khấu theo L/C, Chiết khấu theo Nhờ thu
Print('1')
exec('SELECT DISTINCT customer_id
 INTO b1
from QUYNHNN3.KEO_SERVER16.DBO.T24CRD A
----SERVER16_ANHCP.VPB_WHR2.DBO.T24CRD A
 where VPB_LN_PR in (''LDS03007'', ''LDS03011'',  ''LDS03020'',''LDS03022'')
and ((A.CUS_SEGMENT not in (''KHCN'',''HH'') 
and A.BRANCH_CODE not in (select branch_code from [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289''))
              or (A.CUS_SEGMENT is null and LEFT(A.SECTOR,1)>1))
						and BUSINESS_DATE = '''+@DATE+''' ')


----------select * from  b2

------select  * from b2 where FUND_PROGRAM is null or FUND_PROGRAM ='null'
exec('SELECT DISTINCT customer_id, MAX(BUSINESS_DATE) AS MAX_BUSINESS_DATE,
FUND_PROGRAM = CASE 
WHEN VPB_LN_PR=''LDS03007'' THEN ''TAI TRO XUAT KHAU''
WHEN VPB_LN_PR=''LDS03011'' THEN ''TAI TRO NHAP KHAU''
WHEN VPB_LN_PR=''LDS03020'' THEN ''CHIET KHAU THEO L/C''
WHEN VPB_LN_PR=''LDS03022'' THEN ''CHIET KHAU THEO NHO THU''
END INTO b2
from QUYNHNN3.KEO_SERVER16.DBO.T24CRD  a
--SERVER16_ANHCP.VPB_WHR2.DBO.T24CRD A
 where  customer_id in (select customer_id from b1) and 
 VPB_LN_PR in (''LDS03007'', ''LDS03011'',  ''LDS03020'',''LDS03022'')
and ((A.CUS_SEGMENT not in (''KHCN'',''HH'') 
and A.BRANCH_CODE not in (select branch_code from [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289''))
              or (A.CUS_SEGMENT is null and LEFT(A.SECTOR,1)>1))
						and BUSINESS_DATE < '''+@DATE+'''
GROUP BY customer_id, VPB_LN_PR')

-------- AND (A.CUS_SEGMENT<>''KHCN'' AND A.BRANCH_CODE NOT IN (SELECT BRANCH_CODE FROM [ANHCP].CEO_PERFORMANCE.DBO.BRANCHOFF)
--------                        OR (A.CUS_SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))



----select * from f2
-----5. Hình thức bảo lãnh 

----------5.1. 

PRINT ('4')

exec('SELECT  DISTINCT CUST_ID INTO c1 FROM QUYNHNN3.KEO_SERVER16.DBO.[CRB_ALL]
WHERE BUSINESS_DATE='''+@DATE+''' 
  and GL IN (''9220'',''9260'',''9270'',''9280'',''9210'')
AND  SEGMENT not in (''khcn'')
  and COMPANY not in (''VN0010326'',''VN0010327'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010007'',''VN0010005'',
  ''VN0010009'',''VN0010336'',''VN0010335'',''VN0010260'',''VN0010289'',''VN0010338'',''VN0010010'',''VN0010011'',
  ''VN0010012'',''VN0010344'',''VN0010006'')')




exec('SELECT DISTINCT CUST_ID, MAX(BUSINESS_DATE) AS MAX_BUSINESS_DATE, MUC=''BAO LANH'' INTO c2
from QUYNHNN3.KEO_SERVER16.DBO.[CRB_ALL] A
WHERE  CUST_ID in (select CUST_ID from c1) 
and GL IN (''9220'',''9260'',''9270'',''9280'',''9210'')
AND  SEGMENT not in (''khcn'')
  and COMPANY not in (''VN0010326'',''VN0010327'',''VN0010001'',''VN0010004'',''VN0010008'',''VN0010007'',''VN0010005'',
  ''VN0010009'',''VN0010336'',''VN0010335'',''VN0010260'',''VN0010289'',''VN0010338'',''VN0010010'',''VN0010011'',
  ''VN0010012'',''VN0010344'',''VN0010006'')
AND BUSINESS_DATE<'''+@DATE+'''
GROUP BY CUST_ID')




 ----6. Chuyển tiền quốc tế 
--------*********************** THANH TÓAN QUỐC TẾ
--select * from f2

PRINT ('10')

	  exec('SELECT CIF,ACCTNO,CUSTOMER_NAME,CUS_OPEN_DATE,SEGMENT,DAO,BRANCH_CODE
INTO ##TBL_DP_MAST_TRANG
FROM server74.bicdata.[dbo].[DPTB_MASTER] a
where ((segment not in (''KHCN'',''HH'') and a.branch_code not in (select branch_code from anhcp.CEO_PERFORMANCE.dbo.branchoff 
where branch_code <> ''VN0010289''))
or (segment is null and LEFT(sector,1)>1)) ')

exec('SELECT A.* INTO F1 FROM (
SELECT DISTINCT CREDIT_CIF FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A 
WHERE BUSINESS_DATE='''+@DATE+'''
AND CREDIT_CIF IS NOT NULL
AND FT_SECTOR=''Doanh nghiep''
AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
AND TRANSACTION_TYPE=''IT''
AND CREDIT_account  not like (''%1300700020001'')

UNION
SELECT DISTINCT D.CIF FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
LEFT JOIN ##TBL_DP_MAST_TRANG d
ON d.acctno= A.credit_account
WHERE BUSINESS_DATE='''+@DATE+'''
AND FT_SECTOR=''Doanh nghiep''
AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
AND TRANSACTION_TYPE=''IT''
AND CREDIT_account  not like (''%1300700020001'')

-----*********************
UNION
SELECT DISTINCT DEbIT_CIF FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
WHERE BUSINESS_DATE='''+@DATE+'''
AND DEbIT_CIF IS NOT NULL
AND FT_SECTOR=''Doanh nghiep''
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND TRANSACTION_TYPE=''OT''

UNION
SELECT DISTINCT D.CIF FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
LEFT JOIN ##TBL_DP_MAST_TRANG d
ON d.acctno=A.DEBIT_ACCOUNT
WHERE BUSINESS_DATE='''+@DATE+'''
AND FT_SECTOR=''Doanh nghiep''
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND TRANSACTION_TYPE=''OT'') A ')


  exec('SELECT DISTINCT CREDIT_CIF,max(Max_BUSINESS_DATE) as Max_BUSINESS_DATE INTO f2  FROM (
SELECT DISTINCT CREDIT_CIF, max(BUSINESS_DATE) as Max_BUSINESS_DATE FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A 
WHERE CREDIT_CIF in (select distinct CREDIT_CIF from F1) and BUSINESS_DATE<'''+@DATE+'''
AND CREDIT_CIF IS NOT NULL
AND FT_SECTOR=''Doanh nghiep''
AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
AND TRANSACTION_TYPE=''IT''
AND CREDIT_account  not like (''%1300700020001'')
group by CREDIT_CIF, TRANSACTION_TYPE, debit_account

UNION
SELECT DISTINCT D.CIF, max(BUSINESS_DATE) as Max_BUSINESS_DATE FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
LEFT JOIN ##TBL_DP_MAST_TRANG d
ON d.acctno= A.credit_account
WHERE d.CIF in (select distinct CREDIT_CIF from F1) and BUSINESS_DATE<'''+@DATE+'''
AND FT_SECTOR=''Doanh nghiep''
AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
AND TRANSACTION_TYPE=''IT''
AND CREDIT_account  not like (''%1300700020001'')
group BY D.CIF, TRANSACTION_TYPE, debit_account
-----*********************
UNION
SELECT DISTINCT DEbIT_CIF, max(BUSINESS_DATE) as Max_BUSINESS_DATE FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
WHERE DEbIT_CIF in (select distinct CREDIT_CIF from F1) and BUSINESS_DATE<'''+@DATE+'''
AND DEbIT_CIF IS NOT NULL
AND FT_SECTOR=''Doanh nghiep''
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND TRANSACTION_TYPE=''OT''
GROUP BY DEbIT_CIF, TRANSACTION_TYPE
UNION
SELECT DISTINCT D.CIF, max(BUSINESS_DATE) as Max_BUSINESS_DATE FROM QUYNHNN3.KEO_SERVER16.[dbo].[FT_HIST] A
LEFT JOIN ##TBL_DP_MAST_TRANG d
ON d.acctno=A.DEBIT_ACCOUNT
WHERE d.CIF in (select distinct CREDIT_CIF from F1) and BUSINESS_DATE<'''+@DATE+'''
AND FT_SECTOR=''Doanh nghiep''
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND TRANSACTION_TYPE=''OT''
  GROUP BY D.CIF, TRANSACTION_TYPE) A  group by CREDIT_CIF')


------7. LC, nhờ thu
---------=========**** cÙNG NĂM
----select distinct recid from g2

PRINT ('13')
exec('select DISTINCT APP_CUST_ID INTO g1
from 
 ( SELECT DISTINCT  lc.APP_CUST_ID
  FROM QUYNHNN3.KEO_SERVER16.[dbo].[TBL_LC_DR_VPB] lc
  WHERE (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   AND co_code not in(''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', 
''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
    and ((TYPE=''DR'' AND  LC_TYPE like ''LI%'') 
   OR ( TYPE=''LC'' AND  LC_TYPE like ''LI%'')
    OR (TYPE=''DR'' AND  LC_TYPE like ''DI%'') 
	OR (TYPE=''LC'' AND  LC_TYPE like ''DI%'')
	OR (TYPE=''DR'' AND  LC_TYPE like ''LE%'')
	OR (TYPE=''LC'' AND  LC_TYPE like ''LE%'')
	OR ( TYPE=''DR'' AND  LC_TYPE like ''DE%'')
	OR (TYPE=''LC'' AND  LC_TYPE like ''DE%'') ) 
	AND BUSINESS_DATE='''+@DATE+''') a  ')	

exec('select DISTINCT APP_CUST_ID,max(Max_BUSINESS_DATE) as Max_BUSINESS_DATE  INTO g2
from 
 ( SELECT DISTINCT lc.APP_CUST_ID
 , Max(BUSINESS_DATE) AS Max_BUSINESS_DATE 
  FROM QUYNHNN3.KEO_SERVER16.[dbo].[TBL_LC_DR_VPB] lc
  WHERE APP_CUST_ID in (select APP_CUST_ID from g1) AND co_code not in(''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', 
''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
 and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   and ((TYPE=''DR'' AND  LC_TYPE like ''LI%'') 
   OR ( TYPE=''LC'' AND  LC_TYPE like ''LI%'')
    OR (TYPE=''DR'' AND  LC_TYPE like ''DI%'') 
	OR (TYPE=''LC'' AND  LC_TYPE like ''DI%'')
	OR (TYPE=''DR'' AND  LC_TYPE like ''LE%'')
	OR (TYPE=''LC'' AND  LC_TYPE like ''LE%'')
	OR ( TYPE=''DR'' AND  LC_TYPE like ''DE%'')
	OR (TYPE=''LC'' AND  LC_TYPE like ''DE%'') ) 
   AND BUSINESS_DATE<'''+@DATE+''' group by APP_CUST_ID, TYPE, LC_TYPE) a group by APP_CUST_ID ')					


----------8. Chiết khấu theo DP, DA, LC  (Có thể trùng với phía trên)
-----Chiết khấu theo LC
----- Chiết khấu nhờ thu DP, DA drop table H1
----select * from h1

--PRINT ('16')
--exec('select DISTINCT a.CUSTOMER_ID INTO h1
--from 
-- ( SELECT DISTINCT CUSTOMER_ID
--from KEO_SERVER16.dbo.T24CRD
----[SERVER16_ANHCP].VPB_WHR2.DBO.T24CRD 
--where VPB_LN_PR IN (''LDS03020'',''LDS03010'',''LDS03015'',''LDU03016'',''LDS05001'', ''LDS03022'',''LDS03024'' )
--and BRANCH_CODE not in ( select BRANCH_CODE from ANHCP.CEO_PERFORMANCE.dbo.branchoff where branch_code <> ''VN0010289'')
--   AND BUSINESS_DATE='''+@DATE+''' ) a ')	

--exec('select DISTINCT a.CUSTOMER_ID,max_BUSINESS_DATE INTO h2
--from 
-- ( SELECT DISTINCT CUSTOMER_ID, 
--  max(BUSINESS_DATE) AS max_BUSINESS_DATE 
-- from KEO_SERVER16.dbo.T24CRD
----[SERVER16_ANHCP].VPB_WHR2.DBO.T24CRD 
--where CUSTOMER_ID in (select CUSTOMER_ID from h1)
--AND VPB_LN_PR IN (''LDS03020'',''LDS03010'',''LDS03015'',''LDU03016'',''LDS05001'', ''LDS03022'',''LDS03024'' )
-- and BRANCH_CODE not in ( select BRANCH_CODE from ANHCP.CEO_PERFORMANCE.dbo.branchoff where branch_code <> ''VN0010289'')
--   AND BUSINESS_DATE<'''+@DATE+''' group by CUSTOMER_ID, VPB_LN_PR) a ')					


---------------=========================****************************************
Print('19')


exec('select a.* into Max_Date_Trade 
from (select customer_id, MAX_BUSINESS_DATE  from  b2
union all
select CUST_ID, MAX_BUSINESS_DATE from c2
union all
select CREDIT_CIF, MAX_BUSINESS_DATE from f2
union all
select APP_CUST_ID, MAX_BUSINESS_DATE from g2) a ')

exec('select distinct customer_id, 
MAX(MAX_BUSINESS_DATE) AS MAX_BUSINESS_DATE into q1
from Max_Date_Trade
group by customer_id')

------ KH giao dịch Trade lại sau ít nhất 6 tháng

		exec(' SELECT distinct customer_id INTO kh1 FROM q1 A
		WHERE DATEDIFF(MONTH, MAX_BUSINESS_DATE, '''+@DATE+''' )>= 6
		or MAX_BUSINESS_DATE is null')
--------------------------Xác định KH Trade mới
---- KH mới toe:
--select * from KH1

Print('20')
--select * from q2

exec('select * into q2 from b1
union
select * from c1
union
select * from f1
union
select * from g1')

exec (' select *  into KH0
from q2
where customer_id in (SELECT DISTINCT RECID 
FROM [SERVER74].[BICDATA].[dbo].[CUSTOMER] where segment=''SMEs''
 and year(cus_open_date)='''+@YEAR_NEW+''' )
 and  customer_id not in (select customer_id from SERVER12.ANHCP.DBO.KH_TRADE_MOI  )')


exec('SELECT  business_date='''+@DATE+''', A.* INTO KH_MOI
 FROM (select distinct CUSTOMER_ID from KH1 
union 
select distinct CUSTOMER_ID from KH0) A')


--exec('INSERT SERVER12.ANHCP.DBO.KH_TRADE_MOI
------SERVER12.ANHCP.DBO.[KH_TRADE_MOI_version4] 
----trade2.dbo.KH_TTTM_MOI_version_old
--SELECT FROM_DATE='''+@DATE+''', TO_DATE='''+@DATE+''',A.CUSTOMER_ID, CUS_NAME, BRANCH_NAME_SME, ZONE_ID_SME,
--NEW_CUST= CASE WHEN  CUSTOMER_ID IN (select distinct CUSTOMER_ID from KH0) THEN ''0''
-- WHEN CUSTOMER_ID IN (select distinct CUSTOMER_ID from KH1) THEN ''1''
--END 
--FROM KH_MOI A
-- JOIN [SERVER74].[BICDATA].[dbo].[CUSTOMER] B
--ON A.CUSTOMER_ID=B.RECID
-- JOIN [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] C
--ON B.COMPANY_BOOK_UPDATE=C.BRANCH_ID')


--EXEC('UPDATE SERVER12.ANHCP.DBO.KH_TRADE_MOI
--SET BRANCH_NAME_SME = ''OTHERS''
--WHERE BRANCH_NAME_SME IS NULL ')

--EXEC('UPDATE SERVER12.ANHCP.DBO.KH_TRADE_MOI
--SET ZONE_ID_SME = ''OTHERS''
--WHERE ZONE_ID_SME IS NULL ')

									--delete from SERVER12.ANHCP.DBO.KH_TRADE_MOI   where new_cust='0'
--select* into KH_TRADE_MOI_version_old from SERVER12.ANHCP.DBO.KH_TRADE_MOI order by from_date
--select * from SERVER12.ANHCP.DBO.KH_TRADE_MOI order by from_date
									--select * from KH_TRADE_MOI_version_old
									--delete SERVER12.ANHCP.DBO.KH_TRADE_MOI  

exec ('drop table b1
drop table b2
drop table  c1
drop table  c2
drop table  f1
drop table  f2
drop table  g1
drop table  g2
drop table q1
drop table q2
drop table ##TBL_DP_MAST_TRANG')
exec('dROP TABLE KH_MOI')
exec('DROP TABLE Max_Date_Trade')
exec('drop table KH1')
exec('drop table KH0')


--FETCH NEXT FROM _cursor INTO @DATE;
 
--END
--CLOSE _cursor;
--DEALLOCATE _cursor


--insert SERVER12.ANHCP.DBO.[KH_TRADE_MOI_version4]
--select *   from trade2.dbo.KH_TTTM_MOI_version_old  order by FROM_DATE

--insert SERVER12.ANHCP.DBO.KH_TRADE_MOI
--select * from SERVER12.ANHCP.DBO.[KH_TRADE_MOI_version4] 

--DELETE SERVER12.ANHCP.DBO.KH_TRADE_MOI
--select * into KH_TRADE_MOI_v5_fail from SERVER12.ANHCP.DBO.KH_TRADE_MOI


--select * from SERVER12.ANHCP.DBO.KH_TRADE_MOI

--;WITH table_test AS (
--  SELECT * ,
--     row_number() OVER(PARTITION BY customer_id ORDER BY from_date ) AS [rn]
--  FROM SERVER12.ANHCP.DBO.KH_TRADE_MOI
--)
--delete table_test WHERE [rn] > 1


--delete from SERVER12.ANHCP.DBO.KH_TRADE_MOI where from_date>='20160701'----------153